﻿use TEST;

/* 
* 创建员工表
* Employee
*/

CREATE TABLE Employee
(
	PersonNo varchar(20) NOT NULL PRIMARY KEY, -- 工牌号
    Name varchar(20) collate Chinese_PRC_CS_AS not null, -- 姓名
	Factory varchar(50), -- 工厂
	Department varchar(50) collate Chinese_PRC_CS_AS, -- 部门科室
	Tel char(20) -- 电话号码
);

/*
* 创建夹具表
* Fixture
*/

CREATE TABLE  Fixture
(
	BarCode varCHAR(20) PRIMARY KEY, -- 条形码，由纯数字或者英文符号组成，根据该条形码寻找存放位置
    Name varCHAR(50) collate Chinese_PRC_CS_AS not null, -- 夹具名称
	MaterialType varchar(30), -- 物料类型（长、宽、高等）
	Code varchar(30), -- 物料编码,
	Texture varchar(10), -- 材质： 钢、铝
	ApplicationProject varchar(10) collate Chinese_PRC_CS_AS, -- 应用项目
	Num int, -- 数量
	Position varchar(50) collate Chinese_PRC_CS_AS, -- 存放库位
	PersonNo varchar(20), -- 责任人（工号）
	Price float, -- 价格
);


/*
* 入库表
*/
use TEST;
DROP TABLE WareHouseIn;

CREATE TABLE WareHouseIn
(
    id int primary key IDENTITY(1,1) not null, -- 表格id，自增长序列
	BarCode varchar(20) not null, -- 条码
	PersonNo varchar(20), -- 入库人工号
	Num int, -- 入库数量
	InTime datetime, -- 入库时间
	Constraint PK_whi_emp FOREIGN KEY(PersonNo) REFERENCES Employee(PersonNo), -- 人物外键
	Constraint PK_whi_emb FOREIGN KEY(BarCode) REFERENCES Fixture(BarCode), -- 条码外键
);


/*
* 出库表
*/

use Test;

DROP TABLE WareHouseOut;
CREATE TABLE WareHouseOut
(
	id int primary key IDENTITY(1,1), -- 自增长id，每有一条入库记录，就增长1
	BarCode varchar(20) not null, -- 条形码
	PersonNo varchar(20), -- 出库人工号
	Num int, -- 出库数量
	OutTime datetime, -- 出库时间
	Constraint PK_who_emp FOREIGN KEY(PersonNo) REFERENCES Employee(PersonNo),
	Constraint PK_who_emb FOREIGN KEY(BarCode) REFERENCES Fixture(BarCode), -- 条码外键
);


/*
* 借用表
*/
use TEST;
CREATE TABLE BorrowFixture
(   ID int primary key IDENTITY(1,1), -- 自增长ID
    BarCode varchar(20), -- 条形码
	Num int, -- 借用数量
	PersonNo varchar(20), -- 借用人工号
	BorrowTime datetime, -- 借用时间
	PlanReturn datetime, -- 预计归还时间
	Constraint PK_br_emb FOREIGN KEY(BarCode) REFERENCES Fixture(BarCode), -- 条码外键
	Constraint PK_br_emp FOREIGN KEY(PersonNo) REFERENCES Employee(PersonNo), -- 工号外键
);


/*
* 归还表
*/
use TEST;
CREATE TABLE ReturnFixture
(
	ID int primary key IDENTITY(1,1), -- 自增长ID
    BarCode varchar(20), -- 条形码
	Num int, -- 归还数量
	PersonNo varchar(20), -- 归还人工号
	ReturnTime datetime, -- 归还时间
	Constraint PK_re_emb FOREIGN KEY(BarCode) REFERENCES Fixture(BarCode), -- 条码外键
	Constraint PK_re_emp FOREIGN KEY(PersonNo) REFERENCES Employee(PersonNo), -- 工号外键
);

/*
*  创建外键约束
*/

/* 修改列名 */
use TEST;
exec  sp_rename 'Employee.[JobNumber]', 'PersonNo', 'COLUMN';

/* 修改电话号码 */
alter table Employee
alter column PhoneNumber varchar(20);

/* 修改电话号码名称为Tel */
exec  sp_rename 'Employee.[PhoneNumber]', 'Tel', 'COLUMN';

/* 修改夹具表列名 */
exec  sp_rename 'Fixture.[MaterialCode]', 'Code', 'COLUMN';
exec  sp_rename 'Fixture.[ApplicateItem]', 'ApplicationProject', 'COLUMN';
exec  sp_rename 'Fixture.[MaterialQuantity]', 'Num', 'COLUMN';
exec  sp_rename 'Fixture.[StorageLocation]', 'Position', 'COLUMN';
exec  sp_rename 'Fixture.[PersonInCharge]', 'PersonNo', 'COLUMN';

/*建立外键约束*/

/*建立外键约束前应该保证两列数据的数据类型一致*/
alter table Employee
alter column PersonNo varchar(20) not null;

use TEST;
alter TABLE Fixture add constraint fk_PersonNo
	FOREIGN KEY(PersonNo) references Employee(PersonNo);


exec sp_helpconstraint Employee;

alter table Employee drop constraint PK_Employee;
alter table Employee add constraint PK_Employee primary key(PersonNo);


/* 有关日期和时间 */
select CAST('2023-03-03 13:12:00' AS DATETIME);


use TEST;

SELECT count(*) from Employee;


/* 在T-SQL中 IF ELSE使用方法 如果存在，就更新，同时要开启事务？
IF EXISTS(select * from Fixture Where BarCode = 'WareHouseOut.BarCode')
UPDATE Fixture SET Num = Num + 1 WHERE BarCode = 'WareHouseOut.BarCode'
ELSE
"INSERT INTO Fixture(BarCode, Name, MaterialType, Code, ApplicationProject,Num,Position, Texture, PersonNo, Price) VALUES" +
                      $"('{WareHouseOut.BarCode}', '{WareHouseOut.Name}','{WareHouseOut.MaterialType}','{WareHouseOut.Code}', '{WareHouseOut.ApplicationProject}' ," +
                      $"'{WareHouseOut.Num}', '{WareHouseOut.Position}','{WareHouseOut.Texture}','{WareHouseOut.PersonNo}', '{WareHouseOut.Price}')";
*/

/* 在T-SQL中开启事务
BarCode varchar(20) not null, -- 条码
PersonNo varchar(20), -- 入库人工号
Num int, -- 入库数量
InTime datetime, -- 入库时间


BEGIN TRAN;
INSERT INTO WareHouseOut(BarCode, PersonNo, Num, InTime) VALUES();
COMMIT TRAN;
*/

use TEST;
Select * From WareHouseIn;
Insert INTO WareHouseIn(BarCode, PersonNo, Num, InTime) Values('3132162130000', '5370771', 1, '2023-03-06 11:39:00');

Select id, Fixture.* From WareHouseIn as whi Join  Fixture on whi.BarCode = Fixture.BarCode;

DELETE  FROM WareHouseIn Where InTime is Null;
/*
 现在要修改入库数量，假定夹具表中，一定存在该barcode
 首先要查询到夹具表中，该夹具的数量，再把夹具数量差更新过去，要是没有数量差，就算啦


UPDATE WareHouseIn SET BarCode = '{WareHouseIn.BarCode}', PersonNo = '{WareHouseIn.InPersonNo}', InTime = '{WareHouseIn.InTime}', " + 
Num = {WareHouseIn.Num} WHERE id = '{WareHouseIn.Id}';
 */

 -- 更新主表同时，更新从表
USE TEST
BEGIN  TRAN;
DECLARE @fixtureNum AS INT = (SELECT Num From Fixture WHERE BarCode = '3132162130000');
UPDATE Fixture SET Num += (5 - @fixtureNum) WHERE BarCode = '3132162130000';
UPDATE WareHouseIn SET Num = 5;
COMMIT TRAN;

-- 判断事务是否执行成功？
USE TEST
BEGIN  TRAN;
UPDATE Fixture SET Num += (5 - 2) WHERE BarCode = '3132162130000';
UPDATE WareHouseIn SET Num = '哈哈哈';
COMMIT TRAN;


SELECT * from WareHouseIn;

INSERT INTO WareHouseIn(BarCode, PersonNo) VAlUES ('3132162130003', 5370771);

use TEST;
select * from BorrowFixture;
drop table BorrowReturn;

Select * from BorrowFixture Where BarCode = '1231234125124' order by id 


BEGIN TRAN;
UPDATE Fixture SET Num = Num - 1 WHERE BarCode = '3132162130003'
INSERT INTO BorrowFixture(BarCode, PersonNo, Num, BorrowTime, PlanReturn) VALUES
('3132162130004', '5370771', '1', '2023-03-22 11:03:00', '2023-03-24 11:03:00');
COMMIT TRAN;

select * from Fixture order by BarCode OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Select * from BorrowFixture Where BarCode = '3132162130003' order by ID ;

exec sp_updatestats


Select * from BorrowFixture Where BarCode = '3132162130004' order by ID ;

Select * from BorrowFixture Where BarCode = '{barCode}' order by ID



 BEGIN TRAN;
UPDATE Fixture SET Num = Num + 1 WHERE BarCode = '3132162130004';
 INSERT INTO BorroFixture(BarCode, PersonNo, Num, ReturnTime) VALUEs ('3132162130004', '5370771', '1', '2023/3/23 14:44:40');
 COMMIT TRAN;

 SELECT * FROM Employee Where Name like n;

select id, InTime, WareHouseIn.PersonNo as InPersonNo, WareHouseIn.Num as InNum , Fixture.* from WareHouseIn  Join Fixture on WareHouseIn.BarCode = Fixture.BarCode Where WareHouseIn.BarCode = '3132162130002'order by id DESC;

USE TEST;
BEGIN TRAN;
IF EXISTS(select * from Fixture Where BarCode = '1')
    UPDATE Fixture SET Num = Num + 1 WHERE BarCode = '1'
ELSE
    INSERT INTO Fixture(BarCode, Name, MaterialType, Code, ApplicationProject,Num,Position, Texture, PersonNo, Price) VALUES
    ('1', '测试物料名称', '001', 'cqx0001', '测试项目', 1, '东北', '钢', '', 100);
INSERT INTO WareHouseIn(BarCode, PersonNo, Num, InTime) VALUES('1', '', 1, '2023/5/9 16:28:48' );
COMMIT TRAN;

select * from Fixture Where BarCode = '1'

BEGIN TRAN;
DECLARE @fixtureNum AS INT = (SELECT Num From WareHouseIn WHERE id = 2);
DECLARE @barcode AS varchar(20) = (SELECT BarCode From WareHouseIn WHERE id = 2);
select @barcode, @fixtureNum;
COMMIT TRAN;


UPDATE Fixture SET Num -= @fixtureNum Where BarCode = @barcode;

BEGIN TRAN; UPDATE WareHouseIn SET Num = 4, PersonNo = '5370771', InTime = '2023/5/12 0:00:00' WHERE id = '8030'COMMIT TRAN;

USE TEST;
select id, InTime, WareHouseIn.PersonNo as InPersonNo, WareHouseIn.Num as InNum , Fixture.* from WareHouseIn  Join Fixture on WareHouseIn.BarCode = Fixture.BarCode WHERE 1=1 AND InTime >= CAST('2023/5/1 0:00:00' as Date) order by id DESC;
select id, InTime, WareHouseIn.PersonNo as InPersonNo, WareHouseIn.Num as InNum , Fixture.* from WareHouseIn  Join Fixture on WareHouseIn.BarCode = Fixture.BarCode WHERE 1=1 AND InTime >= CAST('2023/5/1 0:00:00' as Date) order by id DESC;
select id, InTime, WareHouseIn.PersonNo as InPersonNo, WareHouseIn.Num as InNum , Fixture.* from WareHouseIn  Join Fixture on WareHouseIn.BarCode = Fixture.BarCode WHERE 1=1 AND WareHouseIn.PersonNo = '5370771' order by id DESC OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

INSERT INTO BorrowFixture(BarCode, PersonNo, Num, BorrowTime, PlanReturn) VALUES
('00001', '5370771', '1', '0001/1/1 0:00:00', '0001/1/1 0:00:00');


select * from BorrowFixture;

select count(*) from Fixture;

select sum(Num) as totalNum From Fixture;
select sum(Num) from WareHouseOut Where OutTime >= CAST('2023/5/1 0:00:00' as Date);

select sum(Num) from BorrowFixture Where BorrowTime >= CAST('2023/6/1 0:00:00' as Date);

select sum(Num) from BorrowFixture Where BorrowTime >= CAST('2023/6/1 0:00:00' as Date);

select * from WareHouseOut;